<?php

namespace app\admin\logic;

use think\facade\Db;

class OrderPanelLogic
{
    /**
     * 本年月度报货订单趋势图
     * @return Json
     * @author SL
     * @create 2021-08-18
     */
    public function getDataList($param)
    {
        $year = isset($param['year']) && $param['year'] ? $param['year'] : ''; // 年
        $province = isset($param['province']) && $param['province'] ? $param['province'] : ''; // 省份id
        $month = ['1','2','3','4','5','6','7','8','9','10','11','12'];
        $start_time = strtotime(date($year . '-1'));
        $end_time = strtotime(date($year . '-12-31 23:59:59'));

        $where = [];
        $where = [
            ['status', 'in', [2,3,4,5]],
            ['create_time', 'between', "$start_time,$end_time"]
        ];
        $res = [];
        $list = [];
        $res = Db::name('shop_order')->field('count(id) as order_num, sum(pay_amount) as pay_amount, group_concat(uid) as customer_num, month(from_unixtime(create_time)) as month')->where($where)->group('month')->select()->toArray();
        foreach ($res as $k => $v) 
        {
            $res[$k]['customer_num'] = count(array_unique(explode(',', $v['customer_num'])));
        }
        $res = array_column($res, null, 'month');
        foreach ($month as $key => $value) 
        {
            if(isset($res[$value]))
            {
                $list[] = $res[$value];
            }
            else{
                $list[] = [
                    'order_num'     =>0,
                    'pay_amount'    =>'0.00',
                    'customer_num'  =>0,
                    'month'         => $value
                ];
            }
        }
        return $list;
    }

    /**
     * 报货订单年度分月统计
     * @return Json
     * @author SL
     * @create 2021-08-18
     */
    /*public function getRegionDataList($param)
    {
        $year = isset($param['year']) && $param['year'] ? $param['year'] : ''; // 年
        $province = isset($param['province']) && $param['province'] ? $param['province'] : ''; // 省份id
        $month = ['1','2','3','4','5','6','7','8','9','10','11','12'];
        $start_time = strtotime(date($year . '-1'));
        $end_time = strtotime(date($year . '-12-31 23:59:59'));

        $where = [];
        $where = [
            // ['status', 'in', [2,3,4,5]],
            ['create_time', 'between', "$start_time,$end_time"]
        ];
        if($province)
        {
            array_push($where, ['province', '=', $province]);
        }

        $res = Db::name('shop_order')->field('sum(pay_amount) as pay_amount,month(from_unixtime(create_time)) as month,province')->where($where)->group('month,province')->select()->toArray();

        $total_amount = 0;
        $month_amount = []; // 各月所有省金额总计
        $area_amount = []; // 各省所有月金额总计
        foreach($res as $r => $v)
        {
            $total_amount += $v['pay_amount'];
            $list[$v['month'] . '_' . $v['province']] = $v;
            // 各月所有省金额总计
            if(isset($month_amount[$v['month']]))
            {
                $month_amount[$v['month']]['pay_amount'] += $v['pay_amount'];
            }
            else{
                $month_amount[$v['month']] = $v;
            }
            // 各省所有月金额总计
            if(isset($area_amount[$v['province']]))
            {
                $area_amount[$v['province']]['pay_amount'] += $v['pay_amount'];
            }
            else{
                $area_amount[$v['province']] = $v;
            }
        }
        
        $province = array_unique(array_column($res,'province'));
        foreach ($month as $key => $value) 
        {
            // 各月所有省金额总计
            if(isset($month_amount[$value]))
            {
                $month_pay_amount[$value] = $month_amount[$value]['pay_amount'] . '';
            }
            else{
                $month_pay_amount[$value] = '--';
            }

            foreach ($province as $k => $v) 
            {
                if(!isset($list[$value . '_' . $v]))
                {
                    $list[$value . '_' . $v] = [
                        'pay_amount' => '--',
                        'month' => $value,
                        'province' => $v
                    ];
                }
            }
        }
        // 省名称
        $where = [];
        $where = [
            ['level', '=', 1],
            ['id', 'in', $province]
        ];
        $region = Db::name('shop_region')->where($where)->column('name','id');
        foreach($province as $p => $pv)
        {
            // 各省所有月金额总计
            if(isset($area_amount[$pv]))
            {
                $area_pay_amount[$pv]['pay_amount'] = $area_amount[$pv]['pay_amount'] . '';
            }
            else{
                $area_pay_amount[$pv]['pay_amount'] = '--';
            }
            $area_pay_amount[$pv]['province_name'] = $region[$pv];
        }
        $data = [
            'total_amount' => $total_amount,
            'month_amount' => $month_pay_amount ?? [],
            'area_amount'  => $area_pay_amount ?? [],
            'list'         => $list ?? []
        ];
        return $data;
    }*/

    /**
     * 报货订单年度分月统计
     * @return Json
     * @author SL
     * @create 2021-08-18
     */
    public function getRegionDataList($param)
    {
        $year = isset($param['year']) && $param['year'] ? $param['year'] : ''; // 年
        $province = isset($param['province']) && $param['province'] ? $param['province'] : ''; // 省份id
        $month = ['1','2','3','4','5','6','7','8','9','10','11','12'];
        $start_time = strtotime(date($year . '-1'));
        $end_time = strtotime(date($year . '-12-31 23:59:59'));

        $where = [];
        $where = [
            // ['status', 'in', [2,3,4,5]],
            ['create_time', 'between', "$start_time,$end_time"]
        ];
        if($province)
        {
            array_push($where, ['province', '=', $province]);
        }

        $res = Db::name('shop_order')->field('sum(pay_amount) as pay_amount,month(from_unixtime(create_time)) as month,province')->where($where)->group('month,province')->select()->toArray();
        
        $total_amount = 0;
        $month_amount = []; // 各月所有省金额总计
        $area_amount = []; // 各省所有月金额总计
        $list = [];
        foreach($res as $r => $v)
        {
            $total_amount += $v['pay_amount'];
            $list[$v['province']][] = $v;
            // 各月所有省金额总计
            if(isset($month_amount[$v['month']]))
            {
                $month_amount[$v['month']]['pay_amount'] += $v['pay_amount'];
            }
            else{
                $month_amount[$v['month']] = $v;
            }
            // 各省所有月金额总计
            if(isset($area_amount[$v['province']]))
            {
                $area_amount[$v['province']]['pay_amount'] += $v['pay_amount'];
            }
            else{
                $area_amount[$v['province']] = $v;
            }
        }

        foreach ($month as $key => $value) 
        {
            // 各月所有省金额总计
            if(isset($month_amount[$value]))
            {
                $month_pay_amount[$value] = $month_amount[$value]['pay_amount'] . '';
            }
            else{
                $month_pay_amount[$value] = '--';
            }
        }
        $province = array_unique(array_column($res,'province'));
        // 省名称
        $where = [];
        $where = [
            ['level', '=', 1],
            ['id', 'in', $province]
        ];
        $region = Db::name('shop_region')->where($where)->column('name','id');
        foreach($list as $key => $value)
        {
            $valueArr = array_column($value, null, 'month');
            foreach($month as $k => $v)
            {
                $arr[$key]['province_id'] = $key;
                $arr[$key]['province_name'] = $region[$key];
                $arr[$key]['total_amount'] = $area_amount[$key]['pay_amount'] . '' ?? '--';
                if(isset($valueArr[$v]))
                {
                    $arr[$key][$v] = $valueArr[$v]['pay_amount'] . '';
                }
                else{
                    $arr[$key][$v] = '--';
                }
            }
        }
        // 总计
        $totalArr = [
            'province_id' => 0,
            'province_name' => '总计',
            'total_amount' => $total_amount .'',
        ];
        $totalArr = $totalArr + $month_pay_amount;
        array_unshift($arr, $totalArr);
        return $arr;
    }

    /**
     * 客户报货订单年度分月统计
     * @return Json
     * @author SL
     * @create 2021-08-18
     */
    public function getCustomerDataList($param)
    {
        $year = isset($param['year']) && $param['year'] ? $param['year'] : ''; // 年
        $province = isset($param['province']) && $param['province'] ? $param['province'] : ''; // 省份id
        $customer_name = isset($param['customer_name']) && $param['customer_name'] ? $param['customer_name'] : ''; // 客户名称
        $month = ['1','2','3','4','5','6','7','8','9','10','11','12'];
        $start_time = strtotime(date($year . '-1'));
        $end_time = strtotime(date($year . '-12-31 23:59:59'));

        $where = [];
        $where = [
            // ['o.status', 'in', [2,3,4,5]],
            ['o.create_time', 'between', "$start_time,$end_time"]
        ];
        if($province)
        {
            array_push($where, ['o.province', '=', $province]);
        }
        if($customer_name)
        {
            array_push($where, ['c.name', 'like', "%$customer_name%"]);
        }

        // 按月分组,计算每个月所有客户的总计
        $month_amount = Db::name('shop_order')->alias('o')
            ->join('crm_customer c', 'o.uid = c.customer_id')
            ->field('sum(o.pay_amount) as pay_amount,month(from_unixtime(o.create_time)) as month')
            ->where($where)
            ->group('month')
            ->select()->toArray();
        $total_amount = array_sum(array_column($month_amount,'pay_amount'));
        $month_amount = array_column($month_amount,null,'month');
        foreach ($month as $key => $value) 
        {
            // 各月所有客户金额总计
            if(isset($month_amount[$value]))
            {
                $month_pay_amount[$value] = $month_amount[$value]['pay_amount'] . '';
            }
            else{
                $month_pay_amount[$value] = '--';
            }
        }
        // 总条数
        $dataCount = Db::name('shop_order')->alias('o')
            ->join('crm_customer c', 'o.uid = c.customer_id')
            ->field('o.uid')
            ->where($where)
            ->group('o.uid')
            ->count();
        // 按客户分组,计算每个客户所有月的总计
        $res_uid = Db::name('shop_order')->alias('o')
            ->join('crm_customer c', 'o.uid = c.customer_id')
            ->field('sum(o.pay_amount) as total_amount,o.uid,c.name,group_concat(o.id) as order_id,from_unixtime(max(o.create_time)) as last_create_time')
            ->where($where)
            ->group('o.uid')
            ->limit(($param['page'] - 1) * $param['limit'], $param['limit'])->select()->toArray();

        // 获取当前页客户订单数据
        // $uidArr = array_column($res_uid,'uid');
        $order_id = explode(',', implode(',',array_column($res_uid,'order_id')));
        $arr = array_column($res_uid,null,'uid');

        $owhere = [];
        $owhere = [
            ['id', 'in', $order_id]
        ];
        $all_order = Db::name('shop_order')
            ->field('pay_amount,uid,month(from_unixtime(create_time)) as month,create_time')
            ->where($owhere)
            ->select()->toArray();
        foreach($all_order as $key => $value)
        {
            $list[$value['uid']][] = $value;
        }

        foreach($list as $key => $value)
        {
            foreach($value as $kv => $va)
            {
                if(isset($valueArr[$va['month']]))
                {
                    $valueArr[$va['month']] = $valueArr[$va['month']] +  $va['pay_amount'];
                }
                else{
                    $valueArr[$va['month']] = $va['pay_amount'];
                }
            }

            foreach($month as $k => $v)
            {
                if(isset($valueArr[$v]))
                {
                    $arr[$key][$v] = $valueArr[$v] . '';
                }
                else{
                    $arr[$key][$v] = '--';
                }
            }

            unset($valueArr);
        }
        
        // 总计
        $totalArr = [
            'uid' => 0,
            'name' => '总计',
            'total_amount' => $total_amount .'',
            'order_id' => '',
            'last_create_time' => ''
        ];
        $totalArr = $totalArr + $month_pay_amount;

        $data = [];
        $data['dataCount'] = $dataCount ?? 0;

        array_unshift($arr, $totalArr);

        $data['list'] = $arr ?? [];
        return $data;
    }

    /**
     * 业务员报货订单年度分月统计
     * @return Json
     * @author SL
     * @create 2021-08-18
     */
    public function getSalesDataList($param)
    {
        $year = isset($param['year']) && $param['year'] ? $param['year'] : ''; // 年
        $province = isset($param['province']) && $param['province'] ? $param['province'] : ''; // 省份id
        $month = ['1','2','3','4','5','6','7','8','9','10','11','12'];
        $start_time = strtotime(date($year . '-1'));
        $end_time = strtotime(date($year . '-12-31 23:59:59'));

        $where = [];
        $where = [
            // ['o.status', 'in', [2,3,4,5]],
            ['o.create_time', 'between', "$start_time,$end_time"]
        ];
        if ($province) 
        {
            // 根据省份获取部门ID
            $structureIdArr = (new \app\admin\model\Structure())->getStructureOrUserByProvince([$province], 'structure');
            array_push($where, ['u.structure_id', 'in', $structureIdArr]);
        }

        // 按月分组,计算每个月所有业务员的总计
        $month_amount = Db::name('shop_order')->alias('o')
            ->join('admin_user u', 'o.customer_trans_id = u.id')
            ->field('sum(o.pay_amount) as pay_amount,month(from_unixtime(o.create_time)) as month')
            ->where($where)
            ->group('month')
            ->select()->toArray();
        $total_amount = array_sum(array_column($month_amount,'pay_amount'));
        $month_amount = array_column($month_amount,null,'month');
        foreach ($month as $key => $value) 
        {
            // 各月所有业务员金额总计
            if(isset($month_amount[$value]))
            {
                $month_pay_amount[$value] = $month_amount[$value]['pay_amount'] . '';
            }
            else{
                $month_pay_amount[$value] = '--';
            }
        }

        // 总条数
        $dataCount = Db::name('shop_order')->alias('o')
            ->join('admin_user u', 'o.customer_trans_id = u.id')
            ->field('o.customer_trans_id')
            ->where($where)
            ->group('o.customer_trans_id')
            ->count();
        // 按客户分组,计算每个客户所有月的总计
        $res_customerid = Db::name('shop_order')->alias('o')
            ->join('admin_user u', 'o.customer_trans_id = u.id')
            ->field('sum(o.pay_amount) as total_amount,o.customer_trans_id,u.structure_id,u.username,u.realname,group_concat(o.id) as order_id')
            ->where($where)
            ->group('o.customer_trans_id')
            ->limit(($param['page'] - 1) * $param['limit'], $param['limit'])->select()->toArray();

        // 获取员工省份
        $structureList = array_column($res_customerid, 'structure_id', 'customer_trans_id');
        if ($structureList) {
            // 获取员工所在省份
            foreach ($structureList as $key => $val) {
                $strWhere = [
                    ['pid_level', 'like', '%-' . $val . '-%'],
                ];
                $structureIdArr = Db::name('admin_structure')->where($strWhere)->column('id');
                array_push($structureIdArr, $val);
                $structureIdArr = array_unique($structureIdArr);
                $strWhere = [
                    ['structure_id', 'in', $structureIdArr]
                ];
                $provinceArr = Db::name('admin_structure_region')->where($strWhere)->column('province_name');
                $structureNameList[$key] = implode('、', array_unique($provinceArr));
            }
        }
        foreach($res_customerid as $k => $v)
        {
            $res_customerid[$k]['province_name'] = $structureNameList[$v['customer_trans_id']];
        }
        // 获取当前页业务员订单数据
        $order_id = explode(',', implode(',',array_column($res_customerid,'order_id')));
        $arr = array_column($res_customerid,null,'customer_trans_id');

        $owhere = [];
        $owhere = [
            ['id', 'in', $order_id]
        ];
        $all_order = Db::name('shop_order')
            ->field('pay_amount,customer_trans_id,month(from_unixtime(create_time)) as month,create_time')
            ->where($owhere)
            ->select()->toArray();
        foreach($all_order as $key => $value)
        {
            $list[$value['customer_trans_id']][] = $value;
        }

        foreach($list as $key => $value)
        {
            foreach($value as $kv => $va)
            {
                if(isset($valueArr[$va['month']]))
                {
                    $valueArr[$va['month']] = $valueArr[$va['month']] +  $va['pay_amount'];
                }
                else{
                    $valueArr[$va['month']] = $va['pay_amount'];
                }
            }
        
            foreach($month as $k => $v)
            {
                if(isset($valueArr[$v]))
                {
                    $arr[$key][$v] = $valueArr[$v] . '';
                }
                else{
                    $arr[$key][$v] = '--';
                }
            }

            unset($valueArr);
        }
        
        // 总计
        $totalArr = [
            'total_amount' => $total_amount .'',
            'customer_trans_id' => 0,
            'structure_id' => 0,
            'username' => '总计',
            'realname' => '总计',
            'order_id' => '',
            'province_name' => ''
        ];
        $totalArr = $totalArr + $month_pay_amount;

        $data = [];
        $data['dataCount'] = $dataCount ?? 0;

        array_unshift($arr, $totalArr);

        $data['list'] = $arr ?? [];
        return $data;
    }

    /**
     * 客户报货金额统计(TOP5)
     * @return Json
     * @author SL
     * @create 2021-08-18
     */
    public function getCustomerTopDataList($param)
    {
        $start_time = strtotime(date('Y-m'));
        $end_time = time();
        $where = [];
        $where = [
            // ['o.status', 'in', [2,3,4,5]],
            ['o.create_time', 'between', "$start_time,$end_time"]
        ];
        $list = Db::name('shop_order')->alias('o')
            ->join('crm_customer c', 'o.uid = c.customer_id')
            ->join('shop_region r', 'c.province_id = r.id')
            ->field('sum(o.pay_amount) as total_amount,o.uid,c.name as customer_name,r.name as province_name,from_unixtime(max(o.create_time)) as last_create_time')
            ->where($where)
            ->group('o.uid')
            ->order('total_amount desc')
            ->limit(0, 5)
            ->select()->toArray();
        $list = $list ?? [];
        return $list;
    }

    /**
     * 标品销售金额统计(TOP5)
     * @return Json
     * @author SL
     * @create 2021-08-18
     */
    public function getGoodsTopDataList($param)
    {
        $start_time = strtotime(date('Y-m'));
        $end_time = time();
        $where = [];
        $where = [
            // ['o.status', 'in', [2,3,4,5]],
            ['o.create_time', 'between', "$start_time,$end_time"],
            ['g.is_standard_product', '=', 1]
        ];
        $list = Db::name('shop_order_goods')->alias('og')
            ->join('shop_order o', 'og.order_id = o.id')
            ->join('shop_goods g', 'og.goods_id = g.id')
            ->field('sum(og.pay_price * og.number) as total_amount,g.name as goods_name')
            ->where($where)
            ->group('og.goods_id')
            ->order('total_amount desc')
            ->limit(0, 5)
            ->select()->toArray();
        $list = $list ?? [];
        return $list;
    }

    /**
     * 商品报货订单年度分月统计
     * @return Json
     * @author SL
     * @create 2021-08-18
     */
    public function getGoodsDataList($param)
    {
        $year = isset($param['year']) && $param['year'] ? $param['year'] : ''; // 年
        $province = isset($param['province']) && $param['province'] ? $param['province'] : ''; // 省份id
        $month = ['1','2','3','4','5','6','7','8','9','10','11','12'];
        $start_time = strtotime(date($year . '-1'));
        $end_time = strtotime(date($year . '-12-31 23:59:59'));

        $where = [];
        $where = [
            // ['o.status', 'in', [2,3,4,5]],
            ['o.create_time', 'between', "$start_time,$end_time"],
            ['g.is_standard_product', '=', 1]
        ];
        if($province)
        {
            array_push($where, ['o.province', '=', $province]);
        }

        // 按月分组,计算每个月所有商品的总计
        $month_amount = Db::name('shop_order_goods')->alias('og')
            ->join('shop_order o', 'og.order_id = o.id')
            ->join('shop_goods g', 'og.goods_id = g.id')
            ->field('sum(og.pay_price * og.number) as pay_amount,month(from_unixtime(o.create_time)) as month')
            ->where($where)
            ->group('month')
            ->select()->toArray();
        $total_amount = array_sum(array_column($month_amount,'pay_amount'));
        $month_amount = array_column($month_amount,null,'month');
        foreach ($month as $key => $value) 
        {
            // 各月所有商品金额总计
            if(isset($month_amount[$value]))
            {
                $month_pay_amount[$value] = $month_amount[$value]['pay_amount'] . '';
            }
            else{
                $month_pay_amount[$value] = '--';
            }
        }

        // 总条数
        $dataCount = Db::name('shop_order_goods')->alias('og')
            ->join('shop_order o', 'og.order_id = o.id')
            ->join('shop_goods g', 'og.goods_id = g.id')
            ->field('og.goods_id')
            ->where($where)
            ->group('og.goods_id')
            ->count();
        // 按商品分组,计算每个商品所有月的总计
        $res_uid = Db::name('shop_order_goods')->alias('og')
            ->join('shop_order o', 'og.order_id = o.id')
            ->join('shop_goods g', 'og.goods_id = g.id')
            ->field('sum(og.pay_price * og.number) as total_amount,og.goods_id,og.goods_name,group_concat(og.id) as order_goods_id')
            ->where($where)
            ->group('og.goods_id')
            ->limit(($param['page'] - 1) * $param['limit'], $param['limit'])->select()->toArray();

        // 获取当前页商品订单数据
        $order_goods_id = explode(',', implode(',',array_column($res_uid,'order_goods_id')));
        $arr = array_column($res_uid,null,'goods_id');

        $owhere = [];
        $owhere = [
            ['og.id', 'in', $order_goods_id]
        ];
        $all_order_goods = Db::name('shop_order_goods')->alias('og')
            ->join('shop_order o', 'og.order_id = o.id')
            ->field('og.pay_price * og.number as pay_amount,og.goods_id,month(from_unixtime(o.create_time)) as month')
            ->where($owhere)
            ->select()->toArray();

        foreach($all_order_goods as $key => $value)
        {
            $list[$value['goods_id']][] = $value;
        }

        foreach($list as $key => $value)
        {
            foreach($value as $kv => $va)
            {
                if(isset($valueArr[$va['month']]))
                {
                    $valueArr[$va['month']] = $valueArr[$va['month']] +  $va['pay_amount'];
                }
                else{
                    $valueArr[$va['month']] = $va['pay_amount'];
                }
            }
            foreach($month as $k => $v)
            {
                if(isset($valueArr[$v]))
                {
                    $arr[$key][$v] = $valueArr[$v] . '';
                }
                else{
                    $arr[$key][$v] = '--';
                }
            }
            unset($valueArr);
        }
        
        // 总计
        $totalArr = [
            'goods_id' => 0,
            'goods_name' => '总计',
            'total_amount' => $total_amount .'',
            'order_goods_id' => ''
        ];
        $totalArr = $totalArr + $month_pay_amount;

        $data = [];
        $data['dataCount'] = $dataCount ?? 0;

        array_unshift($arr, $totalArr);

        $data['list'] = $arr ?? [];
        return $data;
    }

}
